from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from pylons.database import create_engine
from pylons.database import session_context as ctx

meta = MetaData()

todos_table = Table('todos', meta,
                Column('id', Integer, primary_key=True),
                Column('status', Boolean),
                Column('task', String()),
                Column('completed', Date)
                )

tags_table = Table('tags', meta,
                Column('id', Integer, primary_key=True),
                Column('name', String(150))
                )                

todos_tags_table = Table('todos_tags', meta,
                Column('todo_id', Integer, ForeignKey('todos.id')),
                Column('tag_id', Integer, ForeignKey('tags.id'))
                )
                

class Todo(object):
    def __init__(self, task='', completed='', status=0):
        self.task = task
        self.status = status
        self.completed = completed
    
    def _get_tags(self):
        return self._tags
    
    def _set_tags(self, data):
        """provide a list of tags and it will then determine if the tag exists and 
        what to do with the tag.
        
        If the tag exists it will write the many-to-many.  If not it will write the tag and
        then write the many to many.
        """
        if isinstance(data, basestring):
            data = data.split(",")
        
        # normalize the tags list sent in
        new_tags = [Tag.normalize_tag_names(tag) for tag in data]
        
        # get the current associated tags
        existing_tags = [t.name for t in self._tags]
        
        if not existing_tags == new_tags:
            # Create a transaction for this operation
            trans = ctx.current.create_transaction()
            
            # Clear the old tags so they are not re-written
            self._tags = []
            
            for tag_name in new_tags:
                tag_obj = Tag.get_by(_name=tag_name)
                if not tag_obj:
                    # if the tag doesn't already exist create it in the db
                    tag_obj = Tag(tag_name)
                self._tags.append(tag_obj)
                
            #commit the transaction
            trans.commit()
    
    tags = property(_get_tags, _set_tags)

class Tag(object):
    def __init__(self, name):
        self.name = name
    
    @classmethod
    def normalize_tag_names(self, tag_name):
        """Turn all tag names into a lower case standard format"""
        return tag_name.lower().strip().replace(' ', '+')

    def _get_name(self):
        return self._name
    
    def _set_name(self, name):
        self._name = Tag.normalize_tag_names(name)
    
    name = property(_get_name, _set_name)

todomapper = assign_mapper(ctx, Todo, todos_table, 
                            properties = {
                                '_tags':relation(Tag, secondary=todos_tags_table, lazy=False)
                                })
tagmapper = assign_mapper(ctx, Tag, tags_table, properties={"_name": tags_table.c.name})

'''
if __name__ == '__main__':
    meta = BoundMetaData('mysql://turbogears:bobafett@localhost:3306/hitlist2')

    todos_table = Table('todos', meta,
                    Column('id', Integer, primary_key=True),
                    Column('status', Boolean),
                    Column('task', String()),
                    Column('completed', Date)
                    )

    tags_table = Table('tags', meta,
                    Column('id', Integer, primary_key=True),
                    Column('name', String(150))
                    )                

    todos_tags_table = Table('todos_tags', meta,
                    Column('todo_id', Integer, ForeignKey('todos.id')),
                    Column('tag_id', Integer, ForeignKey('tags.id'))
                    )

    meta.engine.echo = True
    meta.create_all()
'''

        


